﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Rison.DBHelper
{
    enum ExecuteType
    {
        NonQuery,
        Identity
    }

    public abstract class DbBaseHelper
    {
        private string _message = string.Empty;
        private string _tempConnectionString = string.Empty;

        private SqlConnection _dbConnection = null;
        private SqlCommand _sqlCommand = null;
        private SqlDataAdapter _adapter = null;
        private SqlTransaction _transaction = null;
        private Stack stack = new Stack();

        public string Message
        {
            get { return _message; }
        }

        public DbBaseHelper()
        {
        }

        public DbBaseHelper(DbBaseHelper db)
        {
            if (db != null)
            {
                this._transaction = db._transaction;
                this._dbConnection = db._dbConnection;
                this.stack = db.stack;
            }
        }


        #region "  使用临时数据库连接  "
        /// <summary>
        /// 如果要使用特定的数据库连接而不是默认连接时，传入连接串
        /// </summary>
        /// <param name="connectionString"></param>
        protected void SetTempConnectionString(string connectionString)
        {
            _tempConnectionString = connectionString;
        }
        /// <summary>
        /// 使用完临时连接时，需要清空，否则将一直使用该连接
        /// </summary>
        protected void ClearTempConnectionString()
        {
            _tempConnectionString = string.Empty;
        }
        #endregion


        #region "  连接、关闭数据库  "
        private bool Connect()
        {
            _message = string.Empty;

            if (_dbConnection == null)
                _dbConnection = new SqlConnection();

            GetConnectionString();

            if (string.IsNullOrEmpty(_message))
            {
                if (_dbConnection.State == ConnectionState.Closed)
                {
                    try
                    {
                        _dbConnection.Open();
                    }
                    catch (SqlException ex)
                    {
                        StringBuilder msg = new StringBuilder();
                        msg.AppendFormat("数据库连接错误：{0}", ex.Message);

                        if (ex.Number == 17)
                            msg.Append("SQL Server连接配置错误或无权访问！请检查连接配置或用户权限。");

                        _message = msg.ToString();

                        DbExecuteLog.Instance.CreateLog(msg.ToString());

                        throw new Exception(msg.ToString(), ex);
                    }
                }
            }

            return string.IsNullOrEmpty(_message);
        }

        private void GetConnectionString()
        {
            if (string.IsNullOrEmpty(_dbConnection.ConnectionString))
            {
                if (!string.IsNullOrEmpty(_tempConnectionString))
                    _dbConnection.ConnectionString = _tempConnectionString;
                else if (!string.IsNullOrEmpty(DatabaseConfigInfo.Instance.DbConnectionString))
                    _dbConnection.ConnectionString = DatabaseConfigInfo.Instance.DbConnectionString;
                else
                    _message = "未知的数据库连接！";
            }
        }

        private void Close()
        {
            if (_dbConnection.State == ConnectionState.Open)
                _dbConnection.Close();

            try
            {
                _dbConnection.Dispose();
            }
            catch { }
            try
            {
                //_dbConnection = null;
            }
            catch { }
        }
        #endregion


        #region "  事务处理  "
        public void BeginTransaction()
        {
            this.Connect();

            if (stack.Count == 0)
            {
                _transaction = _dbConnection.BeginTransaction("transcation");
                stack.Push("transcation");
            }
            else
            {
                _transaction.Save("transactionSavePoint_" + stack.Count.ToString());
                stack.Push("transactionSavePoint_" + stack.Count.ToString());
            }
        }

        public void CommitTransaction()
        {
            if (_transaction != null)
            {
                try
                {
                    if (stack.Count > 1)
                        stack.Pop();
                    else
                    {
                        stack.Pop();

                        _transaction.Commit();
                        _transaction.Dispose();

                        this.Close();
                    }
                }
                catch (SqlException ex)
                {
                    this.RollBackTransaction();

                    StringBuilder msg = new StringBuilder();
                    msg.AppendFormat("提交数据库错误：{0}", ex.Message);

                    throw new Exception(msg.ToString(), ex);
                }
            }
        }

        public void RollBackTransaction()
        {
            if (_transaction != null)
            {
                if (stack.Count > 1)
                    _transaction.Rollback(stack.Pop().ToString());
                else if (stack.Count == 1)
                {
                    stack.Pop();

                    _transaction.Rollback();

                    _transaction.Dispose();

                    this.Close();
                }
            }
        }
        #endregion


        #region "  ****************  初始化sqlCommand 和 Adapter 对象 *********************  "

        private void InitSqlCommand(string sql)
        {
            InitSqlCommand(sql, false);
        }

        /// <summary>
        /// 初始化command对象。
        /// </summary>
        /// <param name="sql_spName">要执行的sql语句或存储过程的名称</param>
        /// <param name="isProc">是否执行的是存储过程</param>
        private void InitSqlCommand(string sql_spName, bool isProc)
        {
            _sqlCommand = new SqlCommand(sql_spName, _dbConnection);
            if (isProc)
            {
                _sqlCommand.CommandType = CommandType.StoredProcedure;
                _sqlCommand.CommandTimeout = 1800;// '限定超时时间为30分钟
            }

            if (_transaction != null)
                _sqlCommand.Transaction = _transaction;
        }

        private void AppendSqlParamters(List<SqlParameter> sqlParams)
        {
            if (sqlParams != null)
            {
                foreach (SqlParameter p in sqlParams)
                {
                    _sqlCommand.Parameters.Add(p);
                }
            }
        }

        private void InitSqlDataAdapter()
        {
            if (_sqlCommand != null)
            {
                _adapter = new SqlDataAdapter(_sqlCommand);

                if (_transaction != null)
                    _adapter.SelectCommand.Transaction = _transaction;
            }
        }

        private void InitSqlDataAdapter(string sql)
        {
            _adapter = new SqlDataAdapter(sql, _dbConnection);

            if (_transaction != null)
                _adapter.SelectCommand.Transaction = _transaction;
        }

        #endregion


        #region "  执行返回整数  "
        protected int ExecuteNonSql(string sql)
        {
            return Convert.ToInt32(this.ExecuteNonQuery(sql, ExecuteType.NonQuery, null));
        }

        protected int ExecuteNonSql(string sql, List<SqlParameter> sqlParams)
        {
            return Convert.ToInt32(this.ExecuteNonQuery(sql, ExecuteType.NonQuery, sqlParams));
        }

        protected long ExecuteNonSqlIdentity(string sql)
        {
            sql += " SELECT @@IDENTITY AS 'Identity' ";
            return this.ExecuteNonQuery(sql, ExecuteType.Identity, null);
        }

        protected long ExecuteNonSqlIdentity(string sql, List<SqlParameter> sqlParams)
        {
            sql += " SELECT @@IDENTITY AS 'Identity' ";
            return this.ExecuteNonQuery(sql, ExecuteType.Identity, sqlParams);
        }

        private long ExecuteNonQuery(string sql, ExecuteType et, List<SqlParameter> sqlParams)
        {
            try
            {
                this.Connect();
                
                //DBExecutor.Instance.InitParams(_dbConnection, sql, _transaction);
                //return DBExecutor.Instance.ExecuteNonSql(sql);

                InitSqlCommand(sql);
                AppendSqlParamters(sqlParams);                    

                if (et == ExecuteType.NonQuery)
                    return Convert.ToInt64(_sqlCommand.ExecuteNonQuery());
                else if (et == ExecuteType.Identity)
                {
                    object o = _sqlCommand.ExecuteScalar();
                    if (!object.Equals(o, null) && !object.Equals(o, DBNull.Value))
                        return Convert.ToInt64(o);
                }
            }
            catch (SqlException ex)
            {
                _message = DatabaseConfigInfo.Instance.GetSqErrorlMessage(ex, sql);
                throw;
            }
            finally
            {
                if (_transaction == null)
                    this.Close();
            }
            return 0;
        }
        #endregion
        

        #region "  执行返回DataSet  "
        protected DataSet ExecuteSql(string sql, string tablename)
        {
            return ExecuteToDataSet(sql, tablename, null);
        }

        protected DataSet ExecuteSql(DataSet ds, string sql, string tablename)
        {
            return ExecuteToDataSet(sql, tablename, ds);
        }

        private DataSet ExecuteToDataSet(string sql, string tablename, DataSet ds)
        {
            try
            {
                this.Connect();

                this.InitSqlDataAdapter(sql);

                if (ds == null)
                    ds = new DataSet();

                _adapter.Fill(ds, tablename);
                
            }
            catch (SqlException ex)
            {
                _message = DatabaseConfigInfo.Instance.GetSqErrorlMessage(ex, sql);
                throw;
            }
            finally
            {
                if (_transaction == null)
                    this.Close();
            }

            return ds;
        }
        #endregion


        protected DataRow ExecuteSqlToDataRow(string sql)
        {
            DataSet ds = this.ExecuteSql(sql, "row");
            if (ds != null && ds.Tables[0].Rows.Count == 1)
                return ds.Tables[0].Rows[0];

            return null;
        }



        #region "  执行存储过程  "
        protected int ExecuteProcedure(string procedureName)
        {
            return ExecuteProcedure(procedureName, null);
        }

        protected int ExecuteProcedure(string procedureName, List<SqlParameter> prcParams)
        {
            int reVal = -1;
            try
            {
                this.Connect();

                this.InitSqlCommand(procedureName, true);

                this.AppendSqlParamters(prcParams);

                reVal = _sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                _message = DatabaseConfigInfo.Instance.GetSqErrorlMessage(ex, procedureName);
                reVal = ex.Number * -1;
            }
            finally
            {
                if (_sqlCommand != null)
                {
                    _sqlCommand.Dispose(); _sqlCommand = null;
                }

                if (_transaction == null)
                    this.Close();
            }

            return reVal;
        }

        protected DataSet ExecuteProcedure(string procedureName, string tableName, List<SqlParameter> prcParams)
        {
            DataSet data = null;
            try
            {
                this.Connect();

                this.InitSqlCommand(procedureName, true);

                this.AppendSqlParamters(prcParams);

                this.InitSqlDataAdapter();

                _adapter.Fill(data, tableName);
            }
            catch (SqlException ex)
            {
                _message = DatabaseConfigInfo.Instance.GetSqErrorlMessage(ex, procedureName);
                throw;
            }
            finally
            {
                if (_sqlCommand != null)
                {
                    _sqlCommand.Dispose(); _sqlCommand = null;
                }

                if (_transaction == null)
                    this.Close();
            }

            return data;
        }
        #endregion



    }
}
